//Copyright (C) Microsoft Corporation.  All rights reserved.

using System;
using System.Data.Common;
using System.Data.Objects;
using NorthwindEFModel;
using SampleSupport;

namespace SampleQueries
{
    [Title("Entity SQL over ObjectQuery<T>")]
    [Prefix("EntitySQL")]
    class EntitySQLSamples : NorthwindBasedSample
    {

        #region Restriction Operators
        [Category("Restriction Operators")]
        [Title("Where - Entity SQL Literal")]
        [Description("This sample finds all customers in Seattle.")]
        public void EntitySQLR1()
        {
            string entitySQL = "SELECT VALUE c FROM Customers AS c WHERE c.Address.City = 'Seattle';";
            ObjectQuery<Customer> query = context.CreateQuery<Customer>(entitySQL);

            ObjectDumper.Write(query, 1);
        }

        [Category("Restriction Operators")]
        [Title("Where - DateTime Parameter")]
        [Description("This sample find all orders placed before 1997.")]
        public void EntitySQLR2()
        {
            DateTime dt = new DateTime(1997, 1, 1);

            string entitySQL = "SELECT VALUE o FROM Orders AS o WHERE o.OrderDate < @orderDate;";
            ObjectParameter[] queryParams = { new ObjectParameter("orderDate", dt) };
            ObjectQuery<Order> query = context.CreateQuery<Order>(entitySQL, queryParams);

            // show top level objects only
            ObjectDumper.Write(query, 1);
        }

        [Category("Restriction Operators")]
        [Title("Where - Composite predicate")]
        [Description("This sample find Products that have stock below their reorder level and have a units on order of zero.")]
        public void EntitySQLR3()
        {
            string entitySQL = "SELECT VALUE p FROM Products AS p WHERE p.UnitsInStock < p.ReorderLevel AND p.UnitsOnOrder = 0;";
            ObjectQuery<Product> query = context.CreateQuery<Product>(entitySQL);

            // show top level objects only
            ObjectDumper.Write(query, 1);
        }

        [Category("Restriction Operators")]
        [Title("Where - Decimal Parameter")]
        [Description("This sample finds Products that have a UnitPrice less than 10.")]
        public void EntitySQLR4()
        {
            decimal price = 10;

            string entitySQL = "SELECT VALUE p FROM Products AS p WHERE p.UnitPrice < @price;";
            ObjectParameter[] queryParams = { new ObjectParameter("price", price) };
            ObjectQuery<Product> query = context.CreateQuery<Product>(entitySQL, queryParams);

            // show top level objects only
            ObjectDumper.Write(query, 1);
        }

        [Category("Restriction Operators")]
        [Title("Where - Related Entities 1")]
        [Description("This sample finds Orders for Customers in Mexico.")]
        public void EntitySQLR5()
        {
            string entitySQL = "SELECT VALUE o FROM Orders AS o WHERE o.Customer.Address.Country = 'Mexico';";
            ObjectQuery<Order> query = context.CreateQuery<Order>(entitySQL);

            // show top level objects only
            ObjectDumper.Write(query, 1);
        }

        [Category("Restriction Operators")]
        [Title("Where - Related Entities 2")]
        [Description("This sample finds orders for customers not in Mexico or the UK.")]
        public void EntitySQLR7()
        {
            string entitySQL = "SELECT VALUE o FROM Orders AS o WHERE o.Customer.Address.Country <> 'UK' AND o.Customer.Address.Country <> 'Mexico'  ;";
            ObjectQuery<Order> query = context.CreateQuery<Order>(entitySQL);

            // show top level objects only
            ObjectDumper.Write(query, 1);

        }

        [Category("Restriction Operators")]
        [Title("Exists")]
        [Description("This sample finds suppliers for any out-of-stock products.")]
        public void EntitySQLR8()
        {
            string entitySQL = "SELECT VALUE s FROM Suppliers AS s WHERE EXISTS(SELECT p FROM s.Products AS p WHERE p.UnitsInStock = 0);";
            ObjectQuery<Supplier> query = context.CreateQuery<Supplier>(entitySQL);

            // show top level objects only
            ObjectDumper.Write(query, 1);
        }

        #endregion

        #region Aggregate Operators

        [Category("Aggregate Operators")]
        [Title("Count - Simple")]
        [Description("This sample uses COUNT to get the number of Orders.")]
        public void EntitySQLA1()
        {
            string entitySQL = "SELECT VALUE Count(o.OrderID) FROM Orders AS o;";
            ObjectQuery<Int32> query = context.CreateQuery<Int32>(entitySQL);

            ObjectDumper.Write(query, 1);
        }

        [Category("Aggregate Operators")]
        [Title("Count - Predicate 1")]
        [Description("This sample uses COUNT to get the number of Orders placed by Customers in Mexico.")]
        public void EntitySQLA2()
        {
            string entitySQL = "SELECT VALUE Count(o.OrderID) FROM Orders AS o WHERE o.Customer.Address.Country = 'Mexico';";
            ObjectQuery<Int32> query = context.CreateQuery<Int32>(entitySQL);

            ObjectDumper.Write(query, 1);
        }

        [Category("Aggregate Operators")]
        [Title("Count - Predicate 2")]
        [Description("This sample uses COUNT to get the number of Orders shipped to Mexico.")]
        public void EntitySQLA3()
        {
            string entitySQL = "SELECT VALUE Count(o.OrderID) FROM Orders AS o WHERE o.ShipCountry == 'Mexico';";
            ObjectQuery<Int32> query = context.CreateQuery<Int32>(entitySQL);

            ObjectDumper.Write(query, 1);
        }

        [Category("Aggregate Operators")]
        [Title("Sum - Simple 1")]
        [Description("This sample uses SUM to find the total freight over all Orders.")]
        public void EntitySQLA4()
        {
            string entitySQL = "SELECT VALUE Sum(o.Freight) FROM Orders AS o;";
            ObjectQuery<Decimal> query = context.CreateQuery<Decimal>(entitySQL);

            ObjectDumper.Write(query, 1);
        }

        [Category("Aggregate Operators")]
        [Title("Sum - Simple 2")]
        [Description("This sample uses SUM to find the total number of units on order over all Products.")]
        public void EntitySQLA5()
        {
            string entitySQL = "SELECT VALUE Sum(p.UnitsOnOrder) FROM Products AS p;";
            ObjectQuery<Int32> query = context.CreateQuery<Int32>(entitySQL);

            ObjectDumper.Write(query, 1);
        }

        [Category("Aggregate Operators")]
        [Title("Sum - Simple 3")]
        [Description("This sample uses SUM to find the total number of units on order over all Products out-of-stock.")]
        public void EntitySQLA6()
        {
            string entitySQL = "SELECT VALUE Sum(p.UnitsOnOrder) FROM Products AS p WHERE p.UnitsInStock = 0;";
            ObjectQuery<Int32> query = context.CreateQuery<Int32>(entitySQL);

            ObjectDumper.Write(query, 1);
        }

        [Category("Aggregate Operators")]
        [Title("Min - Simple 1")]
        [Description("This sample uses MIN to find the lowest unit price of any Product.")]
        public void EntitySQLA7()
        {
            string entitySQL = "SELECT VALUE Min(p.UnitPrice) FROM Products AS p;";
            ObjectQuery<Decimal> query = context.CreateQuery<Decimal>(entitySQL);

            ObjectDumper.Write(query, 1);
        }

        [Category("Aggregate Operators")]
        [Title("Min - Simple 2")]
        [Description("This sample uses MIN to find the lowest freight of any Order.")]
        public void EntitySQLA8()
        {
            string entitySQL = "SELECT VALUE Min(o.Freight) FROM Orders AS o;";
            ObjectQuery<Decimal> query = context.CreateQuery<Decimal>(entitySQL);

            ObjectDumper.Write(query, 1);
        }

        [Category("Aggregate Operators")]
        [Title("Min - Predicate")]
        [Description("This sample uses MIN to find the lowest freight of any Order shipped to Mexico.")]
        public void EntitySQLA9()
        {
            string entitySQL = "SELECT VALUE Min(o.Freight) FROM Orders AS o WHERE o.ShipCountry = 'Mexico';";
            ObjectQuery<Decimal> query = context.CreateQuery<Decimal>(entitySQL);

            ObjectDumper.Write(query, 1);
        }

        [Category("Aggregate Operators")]
        [Title("Min - Grouping")]
        [Description("This sample uses Min to find the Products that have the lowest unit price in each category.")]
        public void EntitySQLA10()
        {
            string entitySQL = "SELECT Min(p.UnitPrice) AS MinPrice, p.Category.CategoryName FROM Products AS p GROUP BY p.Category.CategoryName;";
            ObjectQuery<DbDataRecord> query = context.CreateQuery<DbDataRecord>(entitySQL);

            ObjectDumper.Write(query, 1);
        }

        [Category("Aggregate Operators")]
        [Title("Max - Simple 1")]
        [Description("This sample uses MAX to find the latest hire date of any Employee.")]
        public void EntitySQLA11()
        {
            string entitySQL = "SELECT VALUE Max(e.HireDate) FROM Employees AS e;";
            ObjectQuery<DateTime> query = context.CreateQuery<DateTime>(entitySQL);

            ObjectDumper.Write(query, 1);
        }

        [Category("Aggregate Operators")]
        [Title("Max - Simple 2")]
        [Description("This sample uses MAX to find the most units in stock of any Product.")]
        public void EntitySQLA12()
        {
            string entitySQL = "SELECT VALUE Max(p.UnitsInStock) FROM Products AS p;";
            ObjectQuery<Int16> query = context.CreateQuery<Int16>(entitySQL);

            ObjectDumper.Write(query, 1);
        }

        [Category("Aggregate Operators")]
        [Title("Max - Predicate")]
        [Description("This sample uses MAX to find the most units in stock of any Product with CategoryID = 1.")]
        public void EntitySQLA13()
        {
            string entitySQL = "SELECT VALUE Max(p.UnitsInStock) FROM Products AS p WHERE p.Category.CategoryID = 1;";
            ObjectQuery<Int16> query = context.CreateQuery<Int16>(entitySQL);

            ObjectDumper.Write(query, 1);
        }

        [Category("Aggregate Operators")]
        [Title("Max - Grouping")]
        [Description("This sample uses MAX to find the Products that have the highest unit price in each category, and returns the result AS an anonoymous type.")]
        public void EntitySQLA14()
        {
            string entitySQL = "SELECT Max(p.UnitPrice) AS MaxPrice, CategoryName FROM Products AS p GROUP BY p.Category.CategoryName AS CategoryName;";
            ObjectQuery<DbDataRecord> query = context.CreateQuery<DbDataRecord>(entitySQL);

            ObjectDumper.Write(query, 1);
        }

        [Category("Aggregate Operators")]
        [Title("Average - Simple 1")]
        [Description("This sample uses AVERAGE to find the average freight of all Orders.")]
        public void EntitySQLA15()
        {
            string entitySQL = "SELECT VALUE Avg(o.Freight) FROM Orders AS o;";
            ObjectQuery<Decimal> query = context.CreateQuery<Decimal>(entitySQL);

            ObjectDumper.Write(query, 1);
        }

        [Category("Aggregate Operators")]
        [Title("Average - Simple 2")]
        [Description("This sample uses AVERAGE to find the average unit price of all Products.")]
        public void EntitySQLA16()
        {
            string entitySQL = "SELECT VALUE Avg(p.UnitPrice) FROM Products AS p;";
            ObjectQuery<Decimal> query = context.CreateQuery<Decimal>(entitySQL);

            ObjectDumper.Write(query, 1);
        }

        [Category("Aggregate Operators")]
        [Title("Average - Predicate")]
        [Description("This sample uses AVERAGE to find the average unit price of all Products with CategoryID = 1.")]
        public void EntitySQLA17()
        {
            string entitySQL = "SELECT VALUE Avg(p.UnitPrice) FROM Products AS p WHERE p.Category.CategoryID = 1;";
            ObjectQuery<Decimal> query = context.CreateQuery<Decimal>(entitySQL);

            ObjectDumper.Write(query, 1);
        }

        [Category("Aggregate Operators")]
        [Title("Average - Grouping 2")]
        [Description("This sample uses AVERAGE to find the average unit price of each category.")]
        public void EntitySQLA19()
        {
            string entitySQL = "SELECT Avg(p.UnitPrice) AS AvgPrice, p.Category.CategoryName FROM Products AS p GROUP BY p.Category.CategoryName;";
            ObjectQuery<DbDataRecord> query = context.CreateQuery<DbDataRecord>(entitySQL);

            ObjectDumper.Write(query, 1);
        }

        [Category("Aggregate Operators")]
        [Title("Aggregate in subquery predicate 1")]
        [Description("This sample retrieves the Product whith the maximum ProductID using MAX() on a subquery of ProductID.")]
        public void EntitySQLA20()
        {
            string entitySql = @"SELECT VALUE p FROM Products AS p WHERE p.ProductID = MAX(SELECT VALUE p2.ProductID FROM Products AS p2);";
            var query = context.CreateQuery<Product>(entitySql);

            ObjectDumper.Write(query, 1);
        }

        [Category("Aggregate Operators")]
        [Title("Aggregate in subquery predicate 2")]
        [Description("This sample retrieves the Product whith the maximum ProductID using ANYELEMENT() on a subquery containing MAX(ProductID).")]
        public void EntitySQLA21()
        {
            string entitySql = @"SELECT VALUE p FROM Products AS p WHERE p.ProductID = ANYELEMENT(SELECT VALUE MAX(p2.ProductID) FROM Products AS p2);";
            var query = context.CreateQuery<Product>(entitySql);

            ObjectDumper.Write(query, 1);

        }


        #endregion

        #region Ordering and Grouping

        [Category("Ordering and Grouping")]
        [Title("Order By - Simple 1")]
        [Description("Select all customers ordered by ContactName.")]
        public void EntitySQLO1()
        {
            string entitySQL = "SELECT VALUE c FROM Customers AS c ORDER BY c.ContactName;";
            ObjectQuery<Customer> query = context.CreateQuery<Customer>(entitySQL);

            ObjectDumper.Write(query, 1);
        }

        [Category("Ordering and Grouping")]
        [Title("Order By - Simple 2")]
        [Description("Select all customers ordered by ContactName descending.")]
        public void EntitySQLO2()
        {
            string entitySQL = "SELECT VALUE c FROM Customers AS c ORDER BY c.ContactName DESC;";
            ObjectQuery<Customer> query = context.CreateQuery<Customer>(entitySQL);

            ObjectDumper.Write(query, 1);
        }

        [Category("Ordering and Grouping")]
        [Title("Order By - Related Entities")]
        [Description("Retrieve customers ordered by number of orders.")]
        public void EntitySQL57()
        {
            string entitySQL = "SELECT VALUE c2.c FROM (SELECT c,  ANYELEMENT(SELECT VALUE Count(o.OrderId) FROM c.Orders AS o) AS orderCount FROM Customers AS c) AS c2 ORDER BY c2.orderCount";
            ObjectQuery<Customer> query = context.CreateQuery<Customer>(entitySQL);

            ObjectDumper.Write(query, 1);
        }

        [Category("Ordering and Grouping")]
        [Title("Order By - Related Entities with Predicate")]
        [Description("Retrieve customers with orders in year 1996 ordered by number of those orders.")]
        public void EntitySQL58()
        {
            string entitySQL = "SELECT VALUE c2.c FROM (SELECT c,  ANYELEMENT(SELECT VALUE Count(o.OrderId) FROM c.Orders AS o WHERE Year(o.OrderDate) = 1996) AS orderCount FROM Customers AS c) AS c2 WHERE c2.OrderCount > 0 ORDER BY c2.orderCount";
            ObjectQuery<Customer> query = context.CreateQuery<Customer>(entitySQL);

            ObjectDumper.Write(query, 1);
        }

        [Category("Ordering and Grouping")]
        [Title("Order By - Multiple Ordering ")]
        [Description("")]
        public void EntitySQL61()
        {
            string entitySQL = "SELECT VALUE c FROM Customers AS c ORDER BY c.CompanyName ASC, c.ContactTitle DESC";
            ObjectQuery<Customer> query = context.CreateQuery<Customer>(entitySQL);

            ObjectDumper.Write(query, 1);
        }

        [Category("Ordering and Grouping")]
        [Title("Order By - Navigation Reference")]
        [Description("Select all orders for a customer ordered by date that the order was placed.")]
        public void EntitySQL63()
        {
            string entitySQL = "SELECT VALUE o FROM Orders AS o WHERE o.Customer.CustomerID = 'ROMEY' ORDER BY o.OrderDate";
            ObjectQuery<Order> query = context.CreateQuery<Order>(entitySQL);

            ObjectDumper.Write(query, 1);
        }

        [Category("Ordering and Grouping")]
        [Title("Grouping - Simple")]
        [Description("Select all dates with number of orders placed.")]
        public void EntitySQL65()
        {
            string entitySQL = "SELECT o.OrderDate, Count(o.OrderID) AS Count FROM Orders AS o GROUP BY o.OrderDate";
            ObjectQuery<DbDataRecord> query = context.CreateQuery<DbDataRecord>(entitySQL);

            ObjectDumper.Write(query, 1);
        }

        #endregion

    }
}

